package com.chenglai.commonutils.common;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.util.List;


/**
 * Excel工具类
 *
 * @author 杨行
 * @date 2021/12/30 15:25
 */
public class ExcelUtil {

    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        CellStyle blackStyle = wb.createCellStyle();
        blackStyle.setWrapText(true);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //字体
        HSSFFont redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        //style.setFont(redFont);
        blackStyle.setFont(redFont);
        //声明列对象
        HSSFCell cell = null;
        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
            if(i>8){
                cell.setCellStyle(blackStyle);
            }
        }
        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
    /**
     * Excel表格导出
     *
     * @param response    HttpServletResponse对象
     * @param excelData   Excel表格的数据，封装为List<List<String>>
     * @param sheetName   sheet的名字
     * @param fileName    导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<List<String>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth, int num) {
        try {
            //声明一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();

            //生成一个表格，设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetName);

            //设置表格列宽度
            sheet.setDefaultColumnWidth(columnWidth);
            //写入List<List<String>>中的数据
            int rowIndex = 0;
            int index = 0;
            for (List<String> data : excelData) {
                //创建一个row行，然后自增1
                HSSFRow row = sheet.createRow(rowIndex++);

                //遍历添加本行数据
                for (int i = 0; i < data.size(); i++) {
                    //创建一个单元格
                    HSSFCell cell = row.createCell(i);

                    //创建一个内容对象
                    HSSFRichTextString text = new HSSFRichTextString(data.get(i));
                    if (data.get(i).contains("http:")) {
                        //插入图片
                        String fileUrl = data.get(i);
                        String head = fileUrl.substring(0, fileUrl.lastIndexOf("/") + 1);
                        String suffix = fileUrl.substring(fileUrl.lastIndexOf("/") + 1);
                        String link = head + URLEncoder.encode(suffix, "UTF-8");
                        // 构造URL
                        URL url = new URL(link);
                        // 打开连接
                        URLConnection con = url.openConnection();
                        //设置请求超时为5s
                        con.setConnectTimeout(8 * 1000);
                        // 输入流
                        InputStream is = con.getInputStream();
                        byte[] bytes = IOUtils.toByteArray(is);
                        @SuppressWarnings("static-access")
                        int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_PNG);
                        CreationHelper helper = workbook.getCreationHelper();
                        Drawing drawing = sheet.createDrawingPatriarch();
                        ClientAnchor anchor = helper.createClientAnchor();
                        // 图片插入坐标
                        anchor.setCol1(num);
                        anchor.setRow1(index);
                        // 插入图片
                        Picture pict = drawing.createPicture(anchor, pictureIdx);
                        pict.resize(2, 1);
                    } else {
                        //将内容对象的文字内容写入到单元格中
                        cell.setCellValue(text);
                    }
                }
                index++;
            }

            //准备将Excel的输出流通过response输出到页面下载
            //八进制输出流
            response.setContentType("application/octet-stream");

            //设置导出Excel的名称
            response.setHeader("Content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");

            //刷新缓冲

            response.flushBuffer();


            //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
            workbook.write(response.getOutputStream());

            //关闭workbook
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}